﻿using System;
using System.Text;
using System.Data.SqlClient;
using System.Collections.Generic;
using System.Data;
using PS.DBUtility;
namespace PS.DAL
{
    //个人绩效考核参数设置
    public partial class EmpKpiConfig
    {

        public bool Exists(int EmpKpiConfigID)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select count(1) from EmpKpiConfig");
            strSql.Append(" where ");
            strSql.Append(" EmpKpiConfigID = @EmpKpiConfigID  ");
            SqlParameter[] parameters = {
					new SqlParameter("@EmpKpiConfigID", SqlDbType.Int,4)
};
            parameters[0].Value = EmpKpiConfigID;

            return DbHelperSQL.Exists(strSql.ToString(), parameters);
        }



        /// <summary>
        /// 增加一条数据
        /// </summary>
        public int Add(PS.Model.EmpKpiConfig model)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("insert into EmpKpiConfig(");
            strSql.Append("EmpKpiType,SelfMarkRate,LeaderMarkRate,EachMarkRate,LowerMarkRate,StartDate,EndDate,EnableMark,EmpKpiDate,KpiValid,Creator,CreateTime,Editor,EditTime,DispOrder,ValidMark,Spare1,Spare2,Spare3,Spare4,SpareDecimal5,SpareDecimal6,SpareDecimal7,SpareDecimal8");
            strSql.Append(") values (");
            strSql.Append("@EmpKpiType,@SelfMarkRate,@LeaderMarkRate,@EachMarkRate,@LowerMarkRate,@StartDate,@EndDate,@EnableMark,@EmpKpiDate,@KpiValid,@Creator,@CreateTime,@Editor,@EditTime,@DispOrder,@ValidMark,@Spare1,@Spare2,@Spare3,@Spare4,@SpareDecimal5,@SpareDecimal6,@SpareDecimal7,@SpareDecimal8");
            strSql.Append(") ");
            strSql.Append(";select @@IDENTITY");
            SqlParameter[] parameters = {
			            new SqlParameter("@EmpKpiType", SqlDbType.Int,4) ,            
                        new SqlParameter("@SelfMarkRate", SqlDbType.Decimal,9) ,            
                        new SqlParameter("@LeaderMarkRate", SqlDbType.Decimal,9) ,            
                        new SqlParameter("@EachMarkRate", SqlDbType.Decimal,9) ,            
                        new SqlParameter("@LowerMarkRate", SqlDbType.Decimal,9) ,            
                        new SqlParameter("@StartDate", SqlDbType.DateTime) ,            
                        new SqlParameter("@EndDate", SqlDbType.DateTime) ,            
                        new SqlParameter("@EnableMark", SqlDbType.Bit,1) ,            
                        new SqlParameter("@EmpKpiDate", SqlDbType.DateTime) ,            
                        new SqlParameter("@KpiValid", SqlDbType.Bit,1) ,            
                        new SqlParameter("@Creator", SqlDbType.VarChar,20) ,            
                        new SqlParameter("@CreateTime", SqlDbType.DateTime) ,            
                        new SqlParameter("@Editor", SqlDbType.VarChar,20) ,            
                        new SqlParameter("@EditTime", SqlDbType.DateTime) ,            
                        new SqlParameter("@DispOrder", SqlDbType.Int,4) ,            
                        new SqlParameter("@ValidMark", SqlDbType.Int,4) ,            
                        new SqlParameter("@Spare1", SqlDbType.VarChar,200) ,            
                        new SqlParameter("@Spare2", SqlDbType.VarChar,200) ,            
                        new SqlParameter("@Spare3", SqlDbType.VarChar,200) ,            
                        new SqlParameter("@Spare4", SqlDbType.VarChar,200) ,            
                        new SqlParameter("@SpareDecimal5", SqlDbType.Decimal,9) ,            
                        new SqlParameter("@SpareDecimal6", SqlDbType.Decimal,9) ,            
                        new SqlParameter("@SpareDecimal7", SqlDbType.Decimal,9) ,            
                        new SqlParameter("@SpareDecimal8", SqlDbType.Decimal,9)             
              
            };

            parameters[0].Value = model.EmpKpiType;
            parameters[1].Value = model.SelfMarkRate;
            parameters[2].Value = model.LeaderMarkRate;
            parameters[3].Value = model.EachMarkRate;
            parameters[4].Value = model.LowerMarkRate;
            parameters[5].Value = model.StartDate;
            parameters[6].Value = model.EndDate;
            parameters[7].Value = model.EnableMark;
            parameters[8].Value = model.EmpKpiDate;
            parameters[9].Value = model.KpiValid;
            parameters[10].Value = model.Creator;
            parameters[11].Value = model.CreateTime;
            parameters[12].Value = model.Editor;
            parameters[13].Value = model.EditTime;
            parameters[14].Value = model.DispOrder;
            parameters[15].Value = model.ValidMark;
            parameters[16].Value = model.Spare1;
            parameters[17].Value = model.Spare2;
            parameters[18].Value = model.Spare3;
            parameters[19].Value = model.Spare4;
            parameters[20].Value = model.SpareDecimal5;
            parameters[21].Value = model.SpareDecimal6;
            parameters[22].Value = model.SpareDecimal7;
            parameters[23].Value = model.SpareDecimal8;

            object obj = DbHelperSQL.GetSingle(strSql.ToString(), parameters);
            if (obj == null)
            {
                return 0;
            }
            else
            {

                return Convert.ToInt32(obj);

            }

        }


        /// <summary>
        /// 更新一条数据
        /// </summary>
        public bool Update(PS.Model.EmpKpiConfig model)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("update EmpKpiConfig set ");

            strSql.Append(" EmpKpiType = @EmpKpiType , ");
            strSql.Append(" SelfMarkRate = @SelfMarkRate , ");
            strSql.Append(" LeaderMarkRate = @LeaderMarkRate , ");
            strSql.Append(" EachMarkRate = @EachMarkRate , ");
            strSql.Append(" LowerMarkRate = @LowerMarkRate , ");
            strSql.Append(" StartDate = @StartDate , ");
            strSql.Append(" EndDate = @EndDate , ");
            strSql.Append(" EnableMark = @EnableMark , ");
            strSql.Append(" EmpKpiDate = @EmpKpiDate , ");
            strSql.Append(" KpiValid = @KpiValid , ");
            strSql.Append(" Creator = @Creator , ");
            strSql.Append(" CreateTime = @CreateTime , ");
            strSql.Append(" Editor = @Editor , ");
            strSql.Append(" EditTime = @EditTime , ");
            strSql.Append(" DispOrder = @DispOrder , ");
            strSql.Append(" ValidMark = @ValidMark , ");
            strSql.Append(" Spare1 = @Spare1 , ");
            strSql.Append(" Spare2 = @Spare2 , ");
            strSql.Append(" Spare3 = @Spare3 , ");
            strSql.Append(" Spare4 = @Spare4 , ");
            strSql.Append(" SpareDecimal5 = @SpareDecimal5 , ");
            strSql.Append(" SpareDecimal6 = @SpareDecimal6 , ");
            strSql.Append(" SpareDecimal7 = @SpareDecimal7 , ");
            strSql.Append(" SpareDecimal8 = @SpareDecimal8  ");
            strSql.Append(" where EmpKpiConfigID=@EmpKpiConfigID ");

            SqlParameter[] parameters = {
			            new SqlParameter("@EmpKpiConfigID", SqlDbType.Int,4) ,            
                        new SqlParameter("@EmpKpiType", SqlDbType.Int,4) ,            
                        new SqlParameter("@SelfMarkRate", SqlDbType.Decimal,9) ,            
                        new SqlParameter("@LeaderMarkRate", SqlDbType.Decimal,9) ,            
                        new SqlParameter("@EachMarkRate", SqlDbType.Decimal,9) ,            
                        new SqlParameter("@LowerMarkRate", SqlDbType.Decimal,9) ,            
                        new SqlParameter("@StartDate", SqlDbType.DateTime) ,            
                        new SqlParameter("@EndDate", SqlDbType.DateTime) ,            
                        new SqlParameter("@EnableMark", SqlDbType.Bit,1) ,            
                        new SqlParameter("@EmpKpiDate", SqlDbType.DateTime) ,            
                        new SqlParameter("@KpiValid", SqlDbType.Bit,1) ,            
                        new SqlParameter("@Creator", SqlDbType.VarChar,20) ,            
                        new SqlParameter("@CreateTime", SqlDbType.DateTime) ,            
                        new SqlParameter("@Editor", SqlDbType.VarChar,20) ,            
                        new SqlParameter("@EditTime", SqlDbType.DateTime) ,            
                        new SqlParameter("@DispOrder", SqlDbType.Int,4) ,            
                        new SqlParameter("@ValidMark", SqlDbType.Int,4) ,            
                        new SqlParameter("@Spare1", SqlDbType.VarChar,200) ,            
                        new SqlParameter("@Spare2", SqlDbType.VarChar,200) ,            
                        new SqlParameter("@Spare3", SqlDbType.VarChar,200) ,            
                        new SqlParameter("@Spare4", SqlDbType.VarChar,200) ,            
                        new SqlParameter("@SpareDecimal5", SqlDbType.Decimal,9) ,            
                        new SqlParameter("@SpareDecimal6", SqlDbType.Decimal,9) ,            
                        new SqlParameter("@SpareDecimal7", SqlDbType.Decimal,9) ,            
                        new SqlParameter("@SpareDecimal8", SqlDbType.Decimal,9)             
              
            };

            parameters[0].Value = model.EmpKpiConfigID;
            parameters[1].Value = model.EmpKpiType;
            parameters[2].Value = model.SelfMarkRate;
            parameters[3].Value = model.LeaderMarkRate;
            parameters[4].Value = model.EachMarkRate;
            parameters[5].Value = model.LowerMarkRate;
            parameters[6].Value = model.StartDate;
            parameters[7].Value = model.EndDate;
            parameters[8].Value = model.EnableMark;
            parameters[9].Value = model.EmpKpiDate;
            parameters[10].Value = model.KpiValid;
            parameters[11].Value = model.Creator;
            parameters[12].Value = model.CreateTime;
            parameters[13].Value = model.Editor;
            parameters[14].Value = model.EditTime;
            parameters[15].Value = model.DispOrder;
            parameters[16].Value = model.ValidMark;
            parameters[17].Value = model.Spare1;
            parameters[18].Value = model.Spare2;
            parameters[19].Value = model.Spare3;
            parameters[20].Value = model.Spare4;
            parameters[21].Value = model.SpareDecimal5;
            parameters[22].Value = model.SpareDecimal6;
            parameters[23].Value = model.SpareDecimal7;
            parameters[24].Value = model.SpareDecimal8;
            int rows = DbHelperSQL.ExecuteSql(strSql.ToString(), parameters);
            if (rows > 0)
            {
                return true;
            }
            else
            {
                return false;
            }
        }


        /// <summary>
        /// 删除一条数据
        /// </summary>
        public bool Delete(int EmpKpiConfigID)
        {

            StringBuilder strSql = new StringBuilder();
            strSql.Append("delete from EmpKpiConfig ");
            strSql.Append(" where EmpKpiConfigID=@EmpKpiConfigID");
            SqlParameter[] parameters = {
					new SqlParameter("@EmpKpiConfigID", SqlDbType.Int,4)
};
            parameters[0].Value = EmpKpiConfigID;


            int rows = DbHelperSQL.ExecuteSql(strSql.ToString(), parameters);
            if (rows > 0)
            {
                return true;
            }
            else
            {
                return false;
            }
        }

        /// <summary>
        /// 删除一条数据
        /// </summary>
        public bool DeleteList(string EmpKpiConfigIDlist)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("delete from EmpKpiConfig ");
            strSql.Append(" where ID in (" + EmpKpiConfigIDlist + ")  ");
            int rows = DbHelperSQL.ExecuteSql(strSql.ToString());
            if (rows > 0)
            {
                return true;
            }
            else
            {
                return false;
            }
        }


        /// <summary>
        /// 得到一个对象实体
        /// </summary>
        public PS.Model.EmpKpiConfig GetModel(int EmpKpiConfigID)
        {

            StringBuilder strSql = new StringBuilder();
            strSql.Append("select EmpKpiConfigID, EmpKpiType, SelfMarkRate, LeaderMarkRate, EachMarkRate, LowerMarkRate, StartDate, EndDate, EnableMark, EmpKpiDate, KpiValid, Creator, CreateTime, Editor, EditTime, DispOrder, ValidMark, Spare1, Spare2, Spare3, Spare4, SpareDecimal5, SpareDecimal6, SpareDecimal7, SpareDecimal8  ");
            strSql.Append("  from EmpKpiConfig ");
            strSql.Append(" where EmpKpiConfigID=@EmpKpiConfigID");
            SqlParameter[] parameters = {
					new SqlParameter("@EmpKpiConfigID", SqlDbType.Int,4)
};
            parameters[0].Value = EmpKpiConfigID;


            PS.Model.EmpKpiConfig model = new PS.Model.EmpKpiConfig();
            DataSet ds = DbHelperSQL.Query(strSql.ToString(), parameters);

            if (ds.Tables[0].Rows.Count > 0)
            {
                if (ds.Tables[0].Rows[0]["EmpKpiConfigID"].ToString() != "")
                {
                    model.EmpKpiConfigID = int.Parse(ds.Tables[0].Rows[0]["EmpKpiConfigID"].ToString());
                }
                if (ds.Tables[0].Rows[0]["EmpKpiType"].ToString() != "")
                {
                    model.EmpKpiType = int.Parse(ds.Tables[0].Rows[0]["EmpKpiType"].ToString());
                }
                if (ds.Tables[0].Rows[0]["SelfMarkRate"].ToString() != "")
                {
                    model.SelfMarkRate = decimal.Parse(ds.Tables[0].Rows[0]["SelfMarkRate"].ToString());
                }
                if (ds.Tables[0].Rows[0]["LeaderMarkRate"].ToString() != "")
                {
                    model.LeaderMarkRate = decimal.Parse(ds.Tables[0].Rows[0]["LeaderMarkRate"].ToString());
                }
                if (ds.Tables[0].Rows[0]["EachMarkRate"].ToString() != "")
                {
                    model.EachMarkRate = decimal.Parse(ds.Tables[0].Rows[0]["EachMarkRate"].ToString());
                }
                if (ds.Tables[0].Rows[0]["LowerMarkRate"].ToString() != "")
                {
                    model.LowerMarkRate = decimal.Parse(ds.Tables[0].Rows[0]["LowerMarkRate"].ToString());
                }
                if (ds.Tables[0].Rows[0]["StartDate"].ToString() != "")
                {
                    model.StartDate = DateTime.Parse(ds.Tables[0].Rows[0]["StartDate"].ToString());
                }
                if (ds.Tables[0].Rows[0]["EndDate"].ToString() != "")
                {
                    model.EndDate = DateTime.Parse(ds.Tables[0].Rows[0]["EndDate"].ToString());
                }
                if (ds.Tables[0].Rows[0]["EnableMark"].ToString() != "")
                {
                    if ((ds.Tables[0].Rows[0]["EnableMark"].ToString() == "1") || (ds.Tables[0].Rows[0]["EnableMark"].ToString().ToLower() == "true"))
                    {
                        model.EnableMark = true;
                    }
                    else
                    {
                        model.EnableMark = false;
                    }
                }
                if (ds.Tables[0].Rows[0]["EmpKpiDate"].ToString() != "")
                {
                    model.EmpKpiDate = DateTime.Parse(ds.Tables[0].Rows[0]["EmpKpiDate"].ToString());
                }
                if (ds.Tables[0].Rows[0]["KpiValid"].ToString() != "")
                {
                    if ((ds.Tables[0].Rows[0]["KpiValid"].ToString() == "1") || (ds.Tables[0].Rows[0]["KpiValid"].ToString().ToLower() == "true"))
                    {
                        model.KpiValid = true;
                    }
                    else
                    {
                        model.KpiValid = false;
                    }
                }
                model.Creator = ds.Tables[0].Rows[0]["Creator"].ToString();
                if (ds.Tables[0].Rows[0]["CreateTime"].ToString() != "")
                {
                    model.CreateTime = DateTime.Parse(ds.Tables[0].Rows[0]["CreateTime"].ToString());
                }
                model.Editor = ds.Tables[0].Rows[0]["Editor"].ToString();
                if (ds.Tables[0].Rows[0]["EditTime"].ToString() != "")
                {
                    model.EditTime = DateTime.Parse(ds.Tables[0].Rows[0]["EditTime"].ToString());
                }
                if (ds.Tables[0].Rows[0]["DispOrder"].ToString() != "")
                {
                    model.DispOrder = int.Parse(ds.Tables[0].Rows[0]["DispOrder"].ToString());
                }
                if (ds.Tables[0].Rows[0]["ValidMark"].ToString() != "")
                {
                    model.ValidMark = int.Parse(ds.Tables[0].Rows[0]["ValidMark"].ToString());
                }
                model.Spare1 = ds.Tables[0].Rows[0]["Spare1"].ToString();
                model.Spare2 = ds.Tables[0].Rows[0]["Spare2"].ToString();
                model.Spare3 = ds.Tables[0].Rows[0]["Spare3"].ToString();
                model.Spare4 = ds.Tables[0].Rows[0]["Spare4"].ToString();
                if (ds.Tables[0].Rows[0]["SpareDecimal5"].ToString() != "")
                {
                    model.SpareDecimal5 = decimal.Parse(ds.Tables[0].Rows[0]["SpareDecimal5"].ToString());
                }
                if (ds.Tables[0].Rows[0]["SpareDecimal6"].ToString() != "")
                {
                    model.SpareDecimal6 = decimal.Parse(ds.Tables[0].Rows[0]["SpareDecimal6"].ToString());
                }
                if (ds.Tables[0].Rows[0]["SpareDecimal7"].ToString() != "")
                {
                    model.SpareDecimal7 = decimal.Parse(ds.Tables[0].Rows[0]["SpareDecimal7"].ToString());
                }
                if (ds.Tables[0].Rows[0]["SpareDecimal8"].ToString() != "")
                {
                    model.SpareDecimal8 = decimal.Parse(ds.Tables[0].Rows[0]["SpareDecimal8"].ToString());
                }

                return model;
            }
            else
            {
                return null;
            }
        }


        /// <summary>
        /// 获得数据列表
        /// </summary>
        public DataSet GetList(string strWhere)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select * ");
            strSql.Append(" FROM EmpKpiConfig ");
            if (strWhere.Trim() != "")
            {
                strSql.Append(" where " + strWhere);
            }
            return DbHelperSQL.Query(strSql.ToString());
        }

        /// <summary>
        /// 获得前几行数据
        /// </summary>
        public DataSet GetList(int Top, string strWhere, string filedOrder)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select ");
            if (Top > 0)
            {
                strSql.Append(" top " + Top.ToString());
            }
            strSql.Append(" * ");
            strSql.Append(" FROM EmpKpiConfig ");
            if (strWhere.Trim() != "")
            {
                strSql.Append(" where " + strWhere);
            }
            strSql.Append(" order by " + filedOrder);
            return DbHelperSQL.Query(strSql.ToString());
        }


        /// <summary>
        /// 保存
        /// </summary>
        /// <param name="model"></param>
        /// <returns></returns>
        public int saveEmpKpiConfig(PS.Model.EmpKpiConfig model, string EMPKPI_M, string EMPKPI_H, string EMPKPI_Y)
        {
            string strWhere = " 1=1 ";
            if (model.EmpKpiType.ToString() == EMPKPI_M)//月度
                strWhere += "and (year(EmpKpiDate)=" + model.EmpKpiDate.AddMonths(-1).Year + " and month(EmpKpiDate)=" + model.EmpKpiDate.AddMonths(-1).Month + ")";

            else if (model.EmpKpiType.ToString() == EMPKPI_H)//半年度
            {
                strWhere += "and (year(EmpKpiDate)=" + model.EmpKpiDate.AddMonths(-1).Year + " and month(EmpKpiDate)=7)";
            }
            else if (model.EmpKpiType.ToString() == EMPKPI_Y)//年度
            {
                if (Convert.ToDateTime(model.EmpKpiDate).Month == 1)//如果是一月
                    strWhere += "and (year(EmpKpiDate)=" + model.EmpKpiDate.Year + " and month(EmpKpiDate)=1)";
                else
                    strWhere += "and (year(EmpKpiDate)=" + model.EmpKpiDate.AddYears(1).Year + " and month(EmpKpiDate)=1)";
            }
            else
                return 0;

            StringBuilder sb = new StringBuilder();
            sb.Append(" if exists (select * from dbo.EmpKpiConfig where ");
            sb.Append(strWhere);
            sb.Append(" and EmpKpiType='" + model.EmpKpiType + "' and ValidMark=1)");
            sb.Append(" update EmpKpiConfig set SelfMarkRate='" + model.SelfMarkRate + "',LeaderMarkRate='" + model.LeaderMarkRate + "',EachMarkRate='" + model.EachMarkRate
                + "',LowerMarkRate='" + model.LowerMarkRate + "',StartDate='" + model.StartDate + "',EndDate='" + model.EndDate + "',EnableMark='" + model.EnableMark + "', KpiValid=1"
                + ",Editor='" + model.Editor + "',EditTime='" + model.EditTime + "'");
            sb.Append(" where ");
            sb.Append(strWhere);
            sb.Append(" and EmpKpiType='" + model.EmpKpiType + "' and ValidMark=1");
            sb.Append(" else");
            sb.Append(" begin ");//设置当前考核参数之前的数据都为历史数据
            sb.Append(" update EmpKpiConfig set KpiValid=0 where EmpKpiType='" + model.EmpKpiType + "' and KpiValid=1 and ValidMark=1 ");
            sb.Append(" ;insert into EmpKpiConfig (EmpKpiType,SelfMarkRate,LeaderMarkRate,EachMarkRate,LowerMarkRate,StartDate,EndDate,EnableMark,EmpKpiDate,");
            sb.Append(" KpiValid,Creator,CreateTime,Editor,EditTime,ValidMark)");
            sb.Append(" values('" + model.EmpKpiType + "','" + model.SelfMarkRate + "','" + model.LeaderMarkRate + "','" + model.EachMarkRate + "','" +
                model.LowerMarkRate + "','" + model.StartDate + "','" + model.EndDate + "','" + model.EnableMark + "',");

            if (model.EmpKpiType.ToString() == EMPKPI_M)//月度
                sb.Append("'" + model.EmpKpiDate.AddMonths(-1) + "'");
            else if (model.EmpKpiType.ToString() == EMPKPI_H)//半年度
            {
                sb.Append("'" + new DateTime(model.EmpKpiDate.Year, 7, 31) + "'");
            }
            else if (model.EmpKpiType.ToString() == EMPKPI_Y)//年度
            {
                if (Convert.ToDateTime(model.EmpKpiDate).Month == 1)//如果是一月
                    sb.Append("'" + new DateTime(model.EmpKpiDate.Year, 1, 31) + "'");
                else
                    sb.Append("'" + new DateTime(model.EmpKpiDate.AddYears(1).Year, 7, 31) + "'");
            }

            sb.Append(" ,'" + model.KpiValid + "','" + model.Creator + "','" + model.CreateTime + "','" + model.Editor + "','" + model.EditTime + "','" + model.@ValidMark + "')");
            sb.Append(" ;select @@IDENTITY");
            sb.Append(" end");

            object obj = DbHelperSQL.GetSingle(sb.ToString());
            if (obj == null)
                return 0;
            else
                return Convert.ToInt32(obj);
        }

        /// <summary>
        /// 获取最后一条信息
        /// </summary> 
        /// <param name="datetimeNow">当前时间</param>
        /// <param name="EmpKpiType">考核参数类型</param>
        /// <returns></returns>
        public DataSet getLastEmpKpiConfig(string datetimeNow, string EmpKpiType, string EMPKPI_M, string EMPKPI_H, string EMPKPI_Y, string isInsert)
        {

            StringBuilder sb = new StringBuilder();
            DateTime dt = System.DateTime.Now;
            string strWhere = " 1=1 ";
            if (EmpKpiType == EMPKPI_M)//月度 
                strWhere += "and (year(EmpKpiDate)=" + dt.AddMonths(-1).Year + " and month(EmpKpiDate)=" + dt.AddMonths(-1).Month + ")";

            else if (EmpKpiType == EMPKPI_H)//半年度
            {
                strWhere += "and (year(EmpKpiDate)=" + dt.AddMonths(-1).Year + " and month(EmpKpiDate)=7)";
            }
            else if (EmpKpiType == EMPKPI_Y)//年度
            {
                if (Convert.ToDateTime(datetimeNow).Month == 1)//如果是一月
                    strWhere += "and (year(EmpKpiDate)=" + dt.Year + " and month(EmpKpiDate)=1)";
                else
                    strWhere += "and (year(EmpKpiDate)=" + dt.AddYears(1).Year + " and month(EmpKpiDate)=1)";
            }
            else
                return null;

            sb.Append(" if exists (select * from dbo.EmpKpiConfig where ");
            sb.Append(strWhere);
            sb.Append(" and EmpKpiType=" + EmpKpiType + " and KpiValid=1 and ValidMark=1)");
            sb.Append(" select EmpKpiConfigID,EmpKpiType,SelfMarkRate,LeaderMarkRate,EachMarkRate,LowerMarkRate,day(StartDate) StartDate,day(EndDate) EndDate,EnableMark,EmpKpiDate, month(EmpKpiDate) EmpKpiMonth,");
            sb.Append(" KpiValid,Creator,CreateTime,Editor,EditTime,DispOrder,ValidMark from dbo.EmpKpiConfig where");
            sb.Append(strWhere);
            sb.Append(" and EmpKpiType=" + EmpKpiType + " and KpiValid=1 and ValidMark=1");
            sb.Append(" else");
            sb.Append(" begin ");
            if (isInsert == "insert")
            {
                sb.Append(insertKpiConfig(datetimeNow, EmpKpiType, EMPKPI_M, EMPKPI_H, EMPKPI_Y, strWhere));
            }
            else
            {
                sb.Append(" select top 1 EmpKpiConfigID,EmpKpiType,SelfMarkRate,LeaderMarkRate,EachMarkRate,LowerMarkRate,day(StartDate) StartDate,day(EndDate) EndDate,EnableMark,EmpKpiDate,month(EmpKpiDate) EmpKpiMonth,");
                sb.Append(" KpiValid,Creator,CreateTime,Editor,EditTime,DispOrder,ValidMark from dbo.EmpKpiConfig where EmpKpiType=" + EmpKpiType + " order by EditTime desc");
            }
            sb.Append(" end");
            return DbHelperSQL.Query(sb.ToString());
        }


        /// <summary>
        /// 添加 一条记录
        /// </summary>
        /// <param name="datetimeNow"></param>
        /// <param name="EmpKpiType"></param>
        /// <param name="EMPKPI_M"></param>
        /// <param name="EMPKPI_H"></param>
        /// <param name="EMPKPI_Y"></param>
        public string insertKpiConfig(string datetimeNow, string EmpKpiType, string EMPKPI_M, string EMPKPI_H, string EMPKPI_Y, string strWhere)
        {
            StringBuilder sb = new StringBuilder();

            DateTime EmpKpiDate = new DateTime();
            string StartDate = "";
            string EndDate = "";

            DateTime dt = DateTime.Now;

            DateTime datetime = new DateTime(dt.Year, dt.AddMonths(1).Month, 1);
            int maxDay = datetime.AddDays(-1).Day;

            if (EmpKpiType == EMPKPI_M)//月度
            {
                EmpKpiDate = dt.AddMonths(-1);
                StartDate = dt.Year.ToString() + "-" + dt.Month.ToString();
                EndDate = dt.Year.ToString() + "-" + dt.Month.ToString();
            }
            else if (EmpKpiType == EMPKPI_H)//半年度
            {
                EmpKpiDate = new DateTime(dt.Year, 7, 31);
                StartDate = dt.Year.ToString() + "-07";
                EndDate = dt.Year.ToString() + "-07";
            }
            else if (EmpKpiType == EMPKPI_Y)//年度
            {
                if (Convert.ToDateTime(datetimeNow).Month == 1)//如果是一月
                {
                    EmpKpiDate = new DateTime(dt.Year, 1, 31);
                    StartDate = dt.Year.ToString() + "-01";
                    EndDate = dt.Year.ToString() + "-01";
                }
                else
                {
                    EmpKpiDate = new DateTime(dt.AddYears(1).Year, 1, 31);
                    StartDate = dt.AddYears(1).Year.ToString() + "-01";
                    EndDate = dt.AddYears(1).Year.ToString() + "-01";
                }
            }

            sb.Append(" if exists (select * from dbo.EmpKpiConfig where ");
            sb.Append(strWhere);
            sb.Append(" and EmpKpiType=" + EmpKpiType + " and ValidMark=1)");
            sb.Append(" begin ");
            sb.Append(" update EmpKpiConfig set KpiValid=1 where");
            sb.Append(strWhere);
            sb.Append(" and EmpKpiType='" + EmpKpiType + "' and ValidMark=1; ");
            sb.Append(" select EmpKpiConfigID,EmpKpiType,SelfMarkRate,LeaderMarkRate,EachMarkRate,LowerMarkRate,day(StartDate) StartDate,day(EndDate) EndDate,EnableMark,EmpKpiDate, month(EmpKpiDate) EmpKpiMonth,");
            sb.Append(" KpiValid,Creator,CreateTime,Editor,EditTime,DispOrder,ValidMark from dbo.EmpKpiConfig where");
            sb.Append(strWhere);
            sb.Append(" and EmpKpiType=" + EmpKpiType + " and KpiValid=1 and ValidMark=1");
            sb.Append(" end");
            sb.Append(" else");
            sb.Append(" begin ");
            sb.Append(" update EmpKpiConfig set KpiValid=0 where EmpKpiType='" + EmpKpiType + "' and KpiValid=1 and ValidMark=1 ");
            sb.Append("  ;insert into dbo.EmpKpiConfig select top 1 EmpKpiType,SelfMarkRate,LeaderMarkRate,");
            sb.Append(" EachMarkRate,LowerMarkRate, '" + StartDate + "'+'-'+convert(varchar(2),day(StartDate)) StartDate,'" + EndDate
                + "'+'-'+convert(varchar(2), case when " + maxDay + ">day(EndDate) then day(EndDate) else " + maxDay + " end)  EndDate,1 as EnableMark,'" + EmpKpiDate + "' as EmpKpiDate,");
            sb.Append(" 1 as KpiValid,Creator,'" + dt + "' as CreateTime,Editor,'" + dt + "' as EditTime,DispOrder,ValidMark ");
            sb.Append(" ,null as Spare1,null as Spare2,null as Spare3,null as Spare4,null as SpareDecimal5,null as SpareDecimal6,null as SpareDecimal7,null as SpareDecimal8");
            sb.Append(" from dbo.EmpKpiConfig where EmpKpiType='" + EmpKpiType + "' order by EditTime desc");
            sb.Append(" ;select EmpKpiConfigID,EmpKpiType,SelfMarkRate,LeaderMarkRate,EachMarkRate,LowerMarkRate,day(StartDate) StartDate,day(EndDate) EndDate,EnableMark,EmpKpiDate, month(EmpKpiDate) EmpKpiMonth,");
            sb.Append(" KpiValid,Creator,CreateTime,Editor,EditTime,DispOrder,ValidMark from dbo.EmpKpiConfig where");
            sb.Append(strWhere);
            sb.Append(" and EmpKpiType='" + EmpKpiType + "' and KpiValid=1 and ValidMark=1");

            sb.Append(" end");


            return sb.ToString();
        }

        /// <summary>
        /// 获取月度考核能查询数据的年份
        /// </summary>
        /// <param name="EmpKpiType"></param>
        /// <returns></returns>
        public DataSet getYearSel(string EmpKpiType)
        {
            StringBuilder sb = new StringBuilder();

            sb.Append("select DISTINCT year(EmpKpiDate) EmpKpiDate from dbo.EmpKpiConfig where ValidMark=1 and EmpKpiType='" + EmpKpiType + "' and EmpKpiConfigID in (select EmpKpiConfigID from dbo.EmpKpiData where ValidMark=1)");
            return DbHelperSQL.Query(sb.ToString());
        }
    }
}

